{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "提示：当前环境pandas版本为0.25，get_price与get_fundamentals_continuously接口panel参数将固定为False\n",
      "注意：0.25以上版本pandas不支持panel，如使用该数据结构和相关函数请注意修改\n",
      "auth success \n"
     ]
    }
   ],
   "source": [
    "from jqdatasdk import *\n",
    "auth(\"13476183321\",\"183321\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['000001.XSHE', '000002.XSHE', '000004.XSHE', '000005.XSHE',\n",
       "       '000006.XSHE', '000007.XSHE', '000008.XSHE', '000009.XSHE',\n",
       "       '000010.XSHE', '000011.XSHE',\n",
       "       ...\n",
       "       '688668.XSHG', '688678.XSHG', '688679.XSHG', '688686.XSHG',\n",
       "       '688698.XSHG', '688699.XSHG', '688777.XSHG', '688788.XSHG',\n",
       "       '688981.XSHG', '689009.XSHG'],\n",
       "      dtype='object', length=4245)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 获取所有标的信息\n",
    "data_index = get_all_securities().index\n",
    "data_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>code</th>\n",
       "      <th>pe_ratio</th>\n",
       "      <th>turnover_ratio</th>\n",
       "      <th>pb_ratio</th>\n",
       "      <th>ps_ratio</th>\n",
       "      <th>pcf_ratio</th>\n",
       "      <th>capitalization</th>\n",
       "      <th>market_cap</th>\n",
       "      <th>circulating_cap</th>\n",
       "      <th>circulating_market_cap</th>\n",
       "      <th>day</th>\n",
       "      <th>pe_ratio_lyr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5329702</td>\n",
       "      <td>002785.XSHE</td>\n",
       "      <td>52.2632</td>\n",
       "      <td>0.0156</td>\n",
       "      <td>1.8852</td>\n",
       "      <td>1.8769</td>\n",
       "      <td>-52.7164</td>\n",
       "      <td>20000.0000</td>\n",
       "      <td>11.6800</td>\n",
       "      <td>5000.000</td>\n",
       "      <td>2.9200</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>57.5431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5329818</td>\n",
       "      <td>300491.XSHE</td>\n",
       "      <td>29.8790</td>\n",
       "      <td>0.0175</td>\n",
       "      <td>3.1845</td>\n",
       "      <td>7.5822</td>\n",
       "      <td>-216.9403</td>\n",
       "      <td>8000.0000</td>\n",
       "      <td>12.0720</td>\n",
       "      <td>2000.000</td>\n",
       "      <td>3.0180</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>32.4072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5566031</td>\n",
       "      <td>600656.XSHG</td>\n",
       "      <td>-11.0190</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-3.1125</td>\n",
       "      <td>-16.7607</td>\n",
       "      <td>-82.9975</td>\n",
       "      <td>19034.3672</td>\n",
       "      <td>12.4675</td>\n",
       "      <td>19032.834</td>\n",
       "      <td>12.4665</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>-12.6121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5329792</td>\n",
       "      <td>300490.XSHE</td>\n",
       "      <td>28.3541</td>\n",
       "      <td>0.0468</td>\n",
       "      <td>2.4352</td>\n",
       "      <td>3.2150</td>\n",
       "      <td>18.3405</td>\n",
       "      <td>10000.0000</td>\n",
       "      <td>13.0900</td>\n",
       "      <td>2500.000</td>\n",
       "      <td>3.2725</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>29.6926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5329461</td>\n",
       "      <td>002777.XSHE</td>\n",
       "      <td>23.2561</td>\n",
       "      <td>0.0485</td>\n",
       "      <td>3.3525</td>\n",
       "      <td>3.4252</td>\n",
       "      <td>43.9888</td>\n",
       "      <td>8000.0000</td>\n",
       "      <td>13.2000</td>\n",
       "      <td>2000.000</td>\n",
       "      <td>3.3000</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>23.6692</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id         code  pe_ratio  turnover_ratio  pb_ratio  ps_ratio  \\\n",
       "0  5329702  002785.XSHE   52.2632          0.0156    1.8852    1.8769   \n",
       "1  5329818  300491.XSHE   29.8790          0.0175    3.1845    7.5822   \n",
       "2  5566031  600656.XSHG  -11.0190          0.0000   -3.1125  -16.7607   \n",
       "3  5329792  300490.XSHE   28.3541          0.0468    2.4352    3.2150   \n",
       "4  5329461  002777.XSHE   23.2561          0.0485    3.3525    3.4252   \n",
       "\n",
       "   pcf_ratio  capitalization  market_cap  circulating_cap  \\\n",
       "0   -52.7164      20000.0000     11.6800         5000.000   \n",
       "1  -216.9403       8000.0000     12.0720         2000.000   \n",
       "2   -82.9975      19034.3672     12.4675        19032.834   \n",
       "3    18.3405      10000.0000     13.0900         2500.000   \n",
       "4    43.9888       8000.0000     13.2000         2000.000   \n",
       "\n",
       "   circulating_market_cap         day  pe_ratio_lyr  \n",
       "0                  2.9200  2015-12-31       57.5431  \n",
       "1                  3.0180  2015-12-31       32.4072  \n",
       "2                 12.4665  2015-12-31      -12.6121  \n",
       "3                  3.2725  2015-12-31       29.6926  \n",
       "4                  3.3000  2015-12-31       23.6692  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 获取单季度/年度财务数据\n",
    "q = query(\n",
    "    valuation\n",
    ").filter(\n",
    "    valuation.code.in_ (list(data_index)) \n",
    ")\n",
    "\n",
    "df = get_fundamentals(q,  statDate=\"2015\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2811, 13)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2015\n",
      "2015q1\n",
      "2015q2\n",
      "2015q3\n",
      "2015q4\n",
      "2016\n",
      "2016q1\n",
      "2016q2\n",
      "2016q3\n",
      "2016q4\n",
      "2017\n",
      "2017q1\n",
      "2017q2\n",
      "2017q3\n",
      "2017q4\n",
      "2018\n",
      "2018q1\n",
      "2018q2\n",
      "2018q3\n",
      "2018q4\n",
      "2019\n",
      "2019q1\n",
      "2019q2\n",
      "2019q3\n",
      "2019q4\n"
     ]
    }
   ],
   "source": [
    "for i in [5,6,7,8,9]:\n",
    "    \n",
    "    for j in [\"\",\"q1\",\"q2\",\"q3\",\"q4\"]:\n",
    "        date = \"201\"+ str(i)\n",
    "        date = date + j\n",
    "        print(date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 统计运行耗时\n",
    "import datetime\n",
    "\n",
    "# start = datetime.datetime.now()\n",
    "\n",
    "# price = data*y\n",
    "\n",
    "# end = datetime.datetime.now()\n",
    "\n",
    "# print('Running time: %s Seconds'%(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Running time: 0:01:01.920516 Seconds\n"
     ]
    }
   ],
   "source": [
    "start = datetime.datetime.now()\n",
    "\n",
    "df = get_fundamentals(q,  statDate= \"2015q1\")\n",
    "for i in [5,6,7,8,9]:\n",
    "    for j in [\"\",\"q1\",\"q2\",\"q3\",\"q4\"]:\n",
    "        date = \"201\"+ str(i)\n",
    "        date = date + j\n",
    "        if date == \"2015q1\":\n",
    "            break\n",
    "        else:\n",
    "            df1 = get_fundamentals(q,  statDate= date)\n",
    "            df = df.append(df1,ignore_index=True)\n",
    "df.to_excel(\"fundamentals.xlsx\")\n",
    "\n",
    "end = datetime.datetime.now()\n",
    "\n",
    "print('Running time: %s Seconds'%(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(73140, 13)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查询2015年之后公告的合并利润表数据，取出合并利润表中本期的营业总收入，归属于母公司的净利润\n",
    "start = datetime.datetime.now()\n",
    "\n",
    "q=query(finance.STK_INCOME_STATEMENT.company_name,\n",
    "        finance.STK_INCOME_STATEMENT.code,\n",
    "        finance.STK_INCOME_STATEMENT.pub_date,\n",
    "        finance.STK_INCOME_STATEMENT.start_date,\n",
    "        finance.STK_INCOME_STATEMENT.end_date,\n",
    "        finance.STK_INCOME_STATEMENT.total_operating_revenue,\n",
    "finance.STK_INCOME_STATEMENT.np_parent_company_owners).filter(finance.STK_INCOME_STATEMENT.code==list(data_index)[0],finance.STK_INCOME_STATEMENT.pub_date>='2015-01-01',finance.STK_INCOME_STATEMENT.report_type==0)\n",
    "df2 = finance.run_query(q)\n",
    "\n",
    "for code in list(data_index)[1:]:\n",
    "    q=query(finance.STK_INCOME_STATEMENT.company_name,\n",
    "        finance.STK_INCOME_STATEMENT.code,\n",
    "        finance.STK_INCOME_STATEMENT.pub_date,\n",
    "        finance.STK_INCOME_STATEMENT.start_date,\n",
    "        finance.STK_INCOME_STATEMENT.end_date,\n",
    "        finance.STK_INCOME_STATEMENT.total_operating_revenue,\n",
    "    finance.STK_INCOME_STATEMENT.np_parent_company_owners).filter(finance.STK_INCOME_STATEMENT.code==code,finance.STK_INCOME_STATEMENT.pub_date>='2015-01-01',finance.STK_INCOME_STATEMENT.report_type==0)\n",
    "    df3 = finance.run_query(q)\n",
    "    df2 = df2.append(df3)\n",
    "df2.to_excel(\"利润表.xlsx\")\n",
    "\n",
    "end = datetime.datetime.now()\n",
    "\n",
    "print('Running time: %s Seconds'%(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
